## Table D.1: Lobbying and Donation Activities:: -------------------------------

## Instructions ----------------------------------------------------------------
# Step 1: Adjust MAIN_DIR to main folder
# Step 2: Adjust DATA_DIR to where data is is located
# Step 3: Adjust FIG_DIR to where output folder is
# Step 4: Run entire script
## setup -----------------------------------------------------------------------

# clean slate
rm(list = ls())

# load packages
library(dplyr)
library(stargazer)
library(lfe)
library(tidyr)
options(scipen=999)

# set directory
MAIN_DIR <- "C:/Users/js.egb/Dropbox/campaign-lobby-paper/replication_package"

# set data folder
DATA_DIR <-  paste(MAIN_DIR, "/data/", sep = "")

# set output folder
FIG_DIR <- paste(MAIN_DIR, "/output/", sep = "")

# file to be read in
file_in1 <- paste0(DATA_DIR, "d_final_20210127.RData") # file to be input
file_in2 <- paste0(DATA_DIR, "compustat_deduped_20210119.RData") # file to be input

# load necessary data
load(file_in1) # lobbying + campaign finance data
load(file_in2) # compustat data

# load("~/campaign-lobby-paper/data/company_politician_dyads/d_final_20210127.RData") # Load Lobbying+FEC data
# 
# load("compustat_deduped_20210119.RData") # Load Compustat data

compustat <- compustat[compustat$loc == "USA",]

d_final <- full_join(d_final, compustat, by = c("gvkey", "report_year"))
d_final_tb2 <- d_final[d_final$report_year == 2013,]

table2 <- as.data.frame(matrix(nrow = 5, ncol = 8)) # Create table shell
rownames(table2) <- c("None", "Lobbying Only", "Donations Only", "Lobbying and Donations", "Total")
colnames(table2) <- c("firm freq", "firm prop", "firm money", "firm money prop",
                      "dyad freq", "dyad prop", "dyad money", "dyad money prop")

### Firm level

d_final_tb2_firm <- d_final_tb2 %>% group_by(gvkey) %>% summarize(lob_tot = ifelse(all(is.na(lobbying_amount)) == T, NA, sum(lobbying_amount, na.rm = T)),
                                                                  don_tot = ifelse(all(is.na(don_tot)) == T, NA, sum(don_tot, na.rm = T)))

table2$`firm freq`[1] <- length(d_final_tb2_firm$gvkey[is.na(d_final_tb2_firm$don_tot) & is.na(d_final_tb2_firm$lob_tot)]) # None freq
table2$`firm prop`[1] <- length(d_final_tb2_firm$gvkey[is.na(d_final_tb2_firm$don_tot) & is.na(d_final_tb2_firm$lob_tot)])/length(d_final_tb2_firm$gvkey) # None prop

table2$`firm freq`[2] <- length(d_final_tb2_firm$gvkey[is.na(d_final_tb2_firm$don_tot) & !is.na(d_final_tb2_firm$lob_tot)]) # Lob only freq
table2$`firm prop`[2] <- length(d_final_tb2_firm$gvkey[is.na(d_final_tb2_firm$don_tot) & !is.na(d_final_tb2_firm$lob_tot)])/length(d_final_tb2_firm$gvkey) # Lob only prop

table2$`firm freq`[3] <- length(d_final_tb2_firm$gvkey[!is.na(d_final_tb2_firm$don_tot) & is.na(d_final_tb2_firm$lob_tot)]) # Don only freq
table2$`firm prop`[3] <- length(d_final_tb2_firm$gvkey[!is.na(d_final_tb2_firm$don_tot) & is.na(d_final_tb2_firm$lob_tot)])/length(d_final_tb2_firm$gvkey) # Don only prop

table2$`firm freq`[4] <- length(d_final_tb2_firm$gvkey[!is.na(d_final_tb2_firm$don_tot) & !is.na(d_final_tb2_firm$lob_tot)]) # Lob + Don freq
table2$`firm prop`[4] <- length(d_final_tb2_firm$gvkey[!is.na(d_final_tb2_firm$don_tot) & !is.na(d_final_tb2_firm$lob_tot)])/length(d_final_tb2_firm$gvkey) # Lob + Don prop

table2$`firm freq`[5] <- length(d_final_tb2_firm$gvkey)
table2$`firm prop`[5] <- 1

table2$`firm money`[1] <- 0
table2$`firm money prop`[1] <- 0

table2$`firm money`[2] <- sum(d_final_tb2_firm$lob_tot[is.na(d_final_tb2_firm$don_tot) & !is.na(d_final_tb2_firm$lob_tot)], na.rm = T) # Lob only money
table2$`firm money prop`[2] <- sum(d_final_tb2_firm$lob_tot[is.na(d_final_tb2_firm$don_tot) & !is.na(d_final_tb2_firm$lob_tot)], na.rm = T)/(sum(d_final_tb2_firm$lob_tot, na.rm = T)+sum(d_final_tb2_firm$don_tot, na.rm = T)) # Lob only money prop

table2$`firm money`[3] <- sum(d_final_tb2_firm$don_tot[!is.na(d_final_tb2_firm$don_tot) & is.na(d_final_tb2_firm$lob_tot)], na.rm = T) # Don only money
table2$`firm money prop`[3] <- sum(d_final_tb2_firm$don_tot[!is.na(d_final_tb2_firm$don_tot) & is.na(d_final_tb2_firm$lob_tot)], na.rm = T)/(sum(d_final_tb2_firm$lob_tot, na.rm = T)+sum(d_final_tb2_firm$don_tot, na.rm = T)) # Lob only money prop

table2$`firm money`[4] <- sum(d_final_tb2_firm$don_tot[!is.na(d_final_tb2_firm$don_tot) & !is.na(d_final_tb2_firm$lob_tot)], na.rm = T)+sum(d_final_tb2_firm$lob_tot[!is.na(d_final_tb2_firm$don_tot) & !is.na(d_final_tb2_firm$lob_tot)], na.rm = T) # Lob + Don money
table2$`firm money prop`[4] <- (sum(d_final_tb2_firm$don_tot[!is.na(d_final_tb2_firm$don_tot) & !is.na(d_final_tb2_firm$lob_tot)], na.rm = T)+sum(d_final_tb2_firm$lob_tot[!is.na(d_final_tb2_firm$don_tot) & !is.na(d_final_tb2_firm$lob_tot)], na.rm = T))/(sum(d_final_tb2_firm$lob_tot, na.rm = T)+sum(d_final_tb2_firm$don_tot, na.rm = T)) # Lob only money prop

table2$`firm money`[5] <- (sum(d_final_tb2_firm$lob_tot, na.rm = T)+sum(d_final_tb2_firm$don_tot, na.rm = T))
table2$`firm money prop`[5] <- 1

### Dyad level

# All inactive dyads
table2$`dyad freq`[1] <- length(unique(d_final_tb2$gvkey))*length(unique(d_final_tb2$govtrack_id))- # All possible dyads (unique firms times unique politicians)
  length(d_final_tb2$gvkey)- # All active dyads plus companies that aren't politically active
  length(d_final_tb2$gvkey[is.na(d_final_tb2$bills) & is.na(d_final_tb2$don_tot)]) # All companies that aren't politically active

# All inactive dyads proportion
table2$`dyad prop`[1] <- (length(unique(d_final_tb2$gvkey))*length(unique(d_final_tb2$govtrack_id))-
                            length(d_final_tb2$gvkey)-
                            length(d_final_tb2$gvkey[is.na(d_final_tb2$bills) & is.na(d_final_tb2$don_tot)]))/(length(unique(d_final_tb2$gvkey))*length(unique(d_final_tb2$govtrack_id)))

# Lob only freq
table2$`dyad freq`[2] <- length(d_final_tb2$gvkey[is.na(d_final_tb2$don_tot)])
# Lob only prop
table2$`dyad prop`[2] <- length(d_final_tb2$gvkey[is.na(d_final_tb2$don_tot)])/(length(unique(d_final_tb2$gvkey))*length(unique(d_final_tb2$govtrack_id)))

# Don only freq
table2$`dyad freq`[3] <- length(d_final_tb2$gvkey[is.na(d_final_tb2$lobbying_amount)])
# Don only prop
table2$`dyad prop`[3] <- length(d_final_tb2$gvkey[is.na(d_final_tb2$lobbying_amount)])/(length(unique(d_final_tb2$gvkey))*length(unique(d_final_tb2$govtrack_id)))

# Lob + Don freq
table2$`dyad freq`[4] <- length(d_final_tb2$gvkey[!is.na(d_final_tb2$don_tot) & !is.na(d_final_tb2$lobbying_amount)])
# Lob + Don prop
table2$`dyad prop`[4] <- length(d_final_tb2$gvkey[!is.na(d_final_tb2$don_tot) & !is.na(d_final_tb2$lobbying_amount)])/(length(unique(d_final_tb2$gvkey))*length(unique(d_final_tb2$govtrack_id)))

table2$`dyad freq`[5] <- (length(unique(d_final_tb2$gvkey))*length(unique(d_final_tb2$govtrack_id)))
table2$`dyad prop`[5] <- 1

# None money
table2$`dyad money`[1] <- 0
table2$`dyad money prop`[1] <- 0

# Lob only money
table2$`dyad money`[2] <- sum(d_final_tb2$lobbying_amount[is.na(d_final_tb2$don_tot)], na.rm = T)
# Lob only money prop
table2$`dyad money prop`[2] <- sum(d_final_tb2$lobbying_amount[is.na(d_final_tb2$don_tot)], na.rm = T)/(sum(d_final_tb2$lobbying_amount, na.rm = T)+sum(d_final_tb2$don_tot, na.rm = T))

# Don only money
table2$`dyad money`[3] <- sum(d_final_tb2$don_tot[is.na(d_final_tb2$bills)], na.rm = T)
# Don only money prop
table2$`dyad money prop`[3] <- sum(d_final_tb2$don_tot[is.na(d_final_tb2$bills)], na.rm = T)/(sum(d_final_tb2$lobbying_amount, na.rm = T)+sum(d_final_tb2$don_tot, na.rm = T))

# Lob + Don money
table2$`dyad money`[4] <- sum(d_final_tb2$don_tot[!is.na(d_final_tb2$bills)], na.rm = T)+sum(d_final_tb2$lobbying_amount[!is.na(d_final_tb2$don_tot)], na.rm = T)
# Lob + Don money prop
table2$`dyad money prop`[4] <- (sum(d_final_tb2$don_tot[!is.na(d_final_tb2$bills)], na.rm = T)+sum(d_final_tb2$lobbying_amount[!is.na(d_final_tb2$don_tot)], na.rm = T))/(sum(d_final_tb2$lobbying_amount, na.rm = T)+sum(d_final_tb2$don_tot, na.rm = T))

# Total money
table2$`dyad money`[5] <- (sum(d_final_tb2$lobbying_amount, na.rm = T)+sum(d_final_tb2$don_tot, na.rm = T))
table2$`dyad money prop`[5] <- 1

### Create beautiful table

colnames(table2) <- c("Frequency", "Proportion", "Money", "Share", "Frequency", "Proportion", "Money", "Share")

library(knitr)
library(kableExtra)

kableExtra::kable(table2, booktabs = T, digits = 2, format = "latex") %>%
  kable_styling(latex_options = "striped") %>%
  add_header_above(c("Political Activity",
                     "Public Companies" = 2, "Donations and Lobbying\n(in USD MM)" = 2,
                     "Dyads" = 2, "Donations and Lobbying\n(in USD MM)" = 2)) %>%
  add_header_above(c(" ", "Firm Level" = 4, "Firm-Politician Level" = 4)) %>%
  save_kable(., file.path(FIG_DIR, "table_d1.tex"))





